package com.ets.Vendor_DAO;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import com.ets.common.DateUtils;
import com.ets.database.DB_Pipe;

/******************************************************************************* 
 The <big><b>VendorDB</b></big> class implements the reader & writer methods of
 the <b>VendorDAO</b> interface to provide the data access to the
 <big><b>Vendor</b></big> table
 <br><br>
 @author D.Merkling - 1:33:38 PM - Aug 22, 2011
 <br><br>
 @version 1.2  
*******************************************************************************/ 
public class VendorDB extends DB_Pipe implements VendorDAO
  {
   public VendorDB()
	 {
	 }

   /*******************************************************************************
	Vendor reader methods
   *******************************************************************************/
   
   public boolean existsVendor (String vndr)
     {
	  String tmpVndr = vndr.trim();
             tmpVndr = tmpVndr.toUpperCase();
      String exists = "SELECT Vndr "      +
			            "FROM vendor "    +
			           "WHERE Vndr = ? ";
      try
		{
         DB_Pipe              = DB_Connect();
	     PreparedStatement ps = DB_Pipe.prepareStatement(exists);
		 ps.setString(1, tmpVndr);
		 ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
		    return true;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
		    return false;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public Vendor getVendor (String str)
     {
	  String tmpStr  = str.trim();
	  String tmpStr2 = tmpStr;
             tmpStr2 = tmpStr2.toUpperCase();
      String sel = "SELECT Vndr, "       +
                          "Typ, "        +
                          "Nm, "         +
                          "Addr, "       +
                          "Phn, "        +
                          "Phn2, "       +
                          "Email, "      +
                          "Email2 "      +
                     "FROM vendor "      +
				    "WHERE Vndr LIKE ? " +
                       "OR Nm   LIKE ? " +
                       "OR Nm   LIKE ?"; 
      try
  		{
         DB_Pipe              = DB_Connect();
  		 PreparedStatement ps = DB_Pipe.prepareStatement(sel);
  		 ps.setString(1, tmpStr2);
  		 ps.setString(2, tmpStr);
  		 ps.setString(3, tmpStr2);
  		 ResultSet         rs = ps.executeQuery();
  		 if (rs.next())
  		   {
		    String Vndr = rs.getString("Vndr");
  			String Typ  = rs.getString("Typ");
  		    String Nm   = rs.getString("Nm");
  		    int    Addr = rs.getInt("Addr");
  		    String Phn  = rs.getString("Phn");
  		    String Phn2 = rs.getString("Phn2");
  		    String Eml  = rs.getString("Eml");
  		    String Eml2 = rs.getString("Eml2");
  		    Vendor v    = new Vendor(Vndr, Typ, Nm, Addr, Phn, Phn2, Eml, Eml2);
  			rs.close();
  			ps.close();
  			return v;
  		   }
  		 else
  		   {
  		    rs.close();
  		    ps.close();
  		    return null;
  		   }
  		}
  	  catch(SQLException sqle)
  		{
  		 //sqle.printStackTrace();	// for debugging
  		 return null;
  		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public Vendor getVendorByID (String vndr)
     {
	  String tmpVndr = vndr.trim();
             tmpVndr = tmpVndr.toUpperCase();
      String sel = "SELECT Vndr, "    +
                          "Typ, "     +
                          "Nm, "      +
                          "Addr, "    +
                          "Phn, "     +
                          "Phn2, "    +
                          "Email, "   +
                          "Email2 "   +
	                 "FROM vendor "   +
			        "WHERE Vndr = ? "; 
      try
        {
         DB_Pipe              = DB_Connect();
         PreparedStatement ps = DB_Pipe.prepareStatement(sel);
         ps.setString(1, tmpVndr);
         ResultSet         rs = ps.executeQuery();
         if (rs.next())
           {
            String Vndr = rs.getString("Vndr");
        	String Typ  = rs.getString("Typ");
        	String Nm   = rs.getString("Nm");
        	int    Addr = rs.getInt("Addr");
        	String Phn  = rs.getString("Phn");
        	String Phn2 = rs.getString("Phn2");
        	String Eml  = rs.getString("Eml");
        	String Eml2 = rs.getString("Eml2");
        	Vendor v    = new Vendor(Vndr, Typ, Nm, Addr, Phn, Phn2, Eml, Eml2);
        	rs.close();
        	ps.close();
        	return v;
           }
         else
           {
            rs.close();
            ps.close();
            return null;
           }
      	}
      catch(SQLException sqle)
      	{
      	 //sqle.printStackTrace();	// for debugging
      	 return null;
      	}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public Vendor getVendorByName (String nm)
     {
	  String tmpNm = nm.trim();
	  String sel = "SELECT Vndr, "  +
                          "Typ, "   +
                          "Nm, "    +
                          "Addr, "  +
                          "Phn, "   +
                          "Phn2, "  +
                          "Email, " +
                          "Email2 " +
                     "FROM vendor " +
		            "WHERE Nm = ? "; 
   	  try
		{
         DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(sel);
		 ps.setString(1, tmpNm);
		 ResultSet         rs = ps.executeQuery();
		 if (rs.next())
		   {
	        String Vndr = rs.getString("Vndr");
		    String Typ  = rs.getString("Typ");
	        String Nm   = rs.getString("Nm");
	        int    Addr = rs.getInt("Addr");
	        String Phn  = rs.getString("Phn");
	        String Phn2 = rs.getString("Phn2");
	        String Eml  = rs.getString("Eml");
	        String Eml2 = rs.getString("Eml2");
	        Vendor v    = new Vendor(Vndr, Typ, Nm, Addr, Phn, Phn2, Eml, Eml2);
		    rs.close();
		    ps.close();
		    return v;
		   }
		 else
		   {
		    rs.close();
		    ps.close();
		    return null;
		   }
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public boolean getVendors (ArrayList<Vendor> vndrs)
     {
	  String qry = "SELECT Vndr, "  +
                          "Typ, "   +
                          "Nm, "    +
                          "Addr, "  +
                          "Phn, "   +
                          "Phn2, "  +
                          "Email, " +
                          "Email2 " +
                     "FROM vendor " +
		            "ORDER BY Vndr ASC";
	  try
		{
	     DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(qry);
		 ResultSet         rs = ps.executeQuery();
		 while(rs.next())
		   {
		    String Vndr = rs.getString("Vndr");
		    String Typ  = rs.getString("Typ");
		    String Nm   = rs.getString("Nm");
		    int    Addr = rs.getInt("Addr");
		    String Phn  = rs.getString("Phn");
		    String Phn2 = rs.getString("Phn2");
		    String Eml  = rs.getString("Email");
		    String Eml2 = rs.getString("Email2");
	        Vendor v    = new Vendor(Vndr, Typ, Nm, Addr, Phn, Phn2, Eml, Eml2);
		    vndrs.add(v);
		   }
		 rs.close();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   /*******************************************************************************
	Vendor writer methods
   *******************************************************************************/
   
   public boolean addVendor (Vendor v)
     {
	  String ins = "INSERT "                    +
	                 "INTO vendor (Vndr, "      +
	                              "Typ, "       +
	                              "Nm, "        +
	                              "Addr, "      +
	                              "Phn, "       +
	                              "Phn2, "      +
	                              "Email, "     +
	                              "Email2, "    +
	                              "TMS_Crtd, "  +
	                              "User_Crtd, " +
	                              "TMS_Updt, "  +
	                              "User_Updt) " +
			       "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(ins);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setString(1, v.getVndr());
		 ps.setString(2, v.getTyp());
		 ps.setString(3, v.getNm());
		 ps.setInt(4, v.getAddr());
		 ps.setString(5, v.getPhn());
		 ps.setString(6, v.getPhn2());
		 ps.setString(7, v.getEmail());
		 ps.setString(8, v.getEmail2());
		 ps.setTimestamp(9, ts);
		 ps.setString(10, "USER0001");
		 ps.setTimestamp(11, ts);
		 ps.setString(12, "USER9999");
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public boolean updateVendor (Vendor v)
     {
	  String upd = "UPDATE vendor "         +
                      "SET Typ       = ?, " +
                          "Nm        = ?, " +
                          "Addr      = ?, " +
                          "Phn       = ?, " +
                          "Phn2      = ?, " +
                          "Email     = ?, " +
                          "Email2    = ?, " +
                          "TMS_Updt  = ?, " +
			              "User_Updt = ? "  +
		            "WHERE Vndr = ?";
	  try
		{
	     DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(upd);
	     Timestamp         ts = DateUtils.getTmstmp();
		 ps.setString(1, v.getTyp());
		 ps.setString(2, v.getNm());
		 ps.setInt(3, v.getAddr());
		 ps.setString(4, v.getPhn());
		 ps.setString(5, v.getPhn2());
		 ps.setString(6, v.getEmail());
		 ps.setString(7, v.getEmail2());
		 ps.setTimestamp(8, ts);
		 ps.setString(9, "USER8888");
		 ps.setString(10, v.getVndr());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
   
   public boolean deleteVendor (Vendor v)
     {
	  String del = "DELETE "         +
	                 "FROM vendor "  +
			        "WHERE Vndr = ?";
	  try
		{
		 DB_Pipe              = DB_Connect();
		 PreparedStatement ps = DB_Pipe.prepareStatement(del);
		 ps.setString(1, v.getVndr());
		 ps.executeUpdate();
		 ps.close();
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	  finally
		{
		 DB_Disconnect(DB_Pipe);
		}
     }
  }
